package com.dao.impl;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.dao.BaseDao;
import com.dao.RedisGeneratorDao;
import com.mapper.GenMapper;
import com.mapper.StudentMapper;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Repository;

import com.dao.StudentDao;
import com.entity.Student;

import javax.annotation.Resource;

@Repository
public class StudentDaoImp extends BaseDao implements StudentDao {

	@Resource
	StudentMapper studentMapper;
	@Resource
	private GenMapper genMapper;
	public List<Student> getAllStudent() throws SQLException, ClassNotFoundException {
		return studentMapper.getAllStudent();
		//return queryRunner.query("select * from student", new BeanListHandler<Student>(Student.class));
	}

	public List<Student> getStudentByName(String name) throws SQLException, ClassNotFoundException {
		System.out.println(name);
		return queryRunner.query("select * from student where name=?", name,
				new BeanListHandler<Student>(Student.class));
	}

	/**
	 * 增加一个学生
	 * 
	 * @param student
	 *            学生对象
	 * @return 返回插入的状态值 数字 为1成功 0失败 -值异常
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws SQLException
	 *             抛出sql异常
	 */
	public int addStudent(Student student) throws IllegalArgumentException, IllegalAccessException, SQLException {
		System.out.println(student.getSno()+" "+ student.getName());
		//Map<String, Object[]> map = insertEntity(student);
		//String sql = map.keySet().iterator().next();
		//Object[] params = map.get(sql);
		//queryRunner.getDataSource().getConnection().setAutoCommit(false);
		//return (queryRunner.insert(sql, new BeanListHandler<Student>(Student.class), params)).size();
	     return studentMapper.insertSelective(student);
	}

	@Override
	public Student loginStudentByName(String name) {
		// TODO Auto-generated method stub
		return studentMapper.loginStudentByName(name);
	}

	public List<Map> select(String methodname) {
		String sql = "";
		switch (methodname) {
			// 统计各门课的选课人数
			case "getCountStudentByCourse":
				sql = "SELECT c.`cno`,cname,COUNT(sc.`scno`) as 'resultval' FROM  course c LEFT JOIN sc ON c.cno = sc.`cno` "
						+ "GROUP BY c.`cno`,cname ORDER BY cname";
				break;
			// 按教室统计各门课的平均分
			case "getAvgScoreByExampointAndCourse":
				sql = "SELECT ec.exampoint,ec.cno,cname,ROUND(IFNULL(AVG(score),0),2) AS 'resultval' " + "FROM ("
						+ "SELECT exampoint,cno,cname FROM ("
						+ "SELECT DISTINCT exampoint FROM sc) t CROSS JOIN  course  c) ec "
						+ "LEFT JOIN sc ON ec.exampoint = sc.`exampoint` AND ec.cno = sc.cno "
						+ "GROUP BY ec.exampoint,ec.cno " + "order by ec.cno,ec.exampoint";
				break;
			// 最受欢迎的N门课在各个教室的平均分
			case "getPouCoureByExampoint":

				sql = "SELECT * FROM (SELECT ec.cno,ec.exampoint, ROUND(IFNULL(AVG(score),0),2) AS 'resultval' FROM (SELECT exampoint,cno FROM (SELECT DISTINCT exampoint FROM sc) t CROSS JOIN  course  c) ec LEFT JOIN sc ON ec.exampoint = sc.`exampoint` AND ec.cno = sc.cno GROUP BY ec.exampoint,ec.cno) temp"

						+ " JOIN "

						+ " (SELECT * FROM (" + " SELECT @rownum:=@rownum+1 AS rownum,t.* FROM (SELECT @rownum:=0) r,"
						+ " (" + " SELECT  c.cno,cname,COUNT(sc.`scno`) AS 'resultval'"
						+ " FROM sc JOIN course c ON c.cno = sc.cno" + " GROUP BY c.cno,cname"
						+ " ORDER BY COUNT(sc.`scno`) DESC" + " ) t" + " ) p"
						+ " WHERE rownum <= 5) q ON temp.cno = q.cno order by temp.cno,exampoint";

			default:
				break;
		}
		return genMapper.select(sql);

	}
}
